package edu.uoc.tdp.pac4.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import edu.uoc.tdp.pac4.beans.Aula;
import edu.uoc.tdp.pac4.beans.Course;
import edu.uoc.tdp.pac4.beans.Group;
import edu.uoc.tdp.pac4.beans.Shift;
import edu.uoc.tdp.pac4.beans.Usuari;

public class GroupManager {

	Connection con = null;
	PreparedStatement ptmt = null;
	ResultSet rs = null;

	public GroupManager() {

	}

	private Connection getConnection() throws SQLException {
		Connection conn = null;
		conn = DiskManager.getInstance().getConnection();
		return conn;
	}

	/*
	 * Saves a group into the database
	 * 
	 * @param group
	 */
	public void save(Group group) {

		try {
			con = getConnection();

			String querystring = "INSERT INTO GRUPO(nombre, maxalumnos, plazasdisponibles, fechainicio, fechafin, grupovigente, idcurso, turno) "
					+ "VALUES(?,?,?,?,?,?,?,?)";

			ptmt = con.prepareStatement(querystring);
			ptmt.setString(1, group.getName());
			ptmt.setInt(2, group.getMaxStudents());
			ptmt.setInt(3, group.getAvailableSpots());
			ptmt.setDate(4, new java.sql.Date(group.getStartDate().getTime()));
			ptmt.setDate(5, new java.sql.Date(group.getFinalDate().getTime()));
			ptmt.setBoolean(6, group.isActiveGroup());
			ptmt.setInt(7, group.getCourse().getCourseCode());
			ptmt.setInt(8, Shift.MORNING.equals(group.getShift()) ? 0 : 1);

			ptmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

	}

	/**
	 * Updates a group into the database
	 * 
	 * @param group
	 */
	public void update(Group group) {
		try {
			String querystring = "UPDATE GRUPO SET nombre=?, maxalumnos=?, plazasdisponibles=?, fechainicio=?, fechafin=?, "
					+ "grupovigente = ?, idcurso = ?, turno = ? "
					+ "WHERE grupoid=?";

			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			ptmt.setString(1, group.getName());
			ptmt.setInt(2, group.getMaxStudents());
			ptmt.setInt(3, group.getAvailableSpots());
			ptmt.setDate(4, new java.sql.Date(group.getStartDate().getTime()));
			ptmt.setDate(5, new java.sql.Date(group.getFinalDate().getTime()));
			ptmt.setBoolean(6, group.isActiveGroup());
			ptmt.setInt(7, group.getCourse().getCourseCode());
			ptmt.setInt(8, Shift.MORNING.equals(group.getShift()) ? 0 : 1);
			ptmt.setInt(9, group.getGroupId());

			ptmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

	}

	/**
	 * Finds all groups
	 * 
	 * @return
	 */
	public List<Group> findAll() {
		List<Group> groups = new ArrayList<Group>();
		Group group = null;
		try {
			String querystring = "SELECT grupoid, nombre, maxalumnos, plazasdisponibles, fechainicio, fechafin, grupovigente, turno, idcurso, idaula FROM GRUPO";
			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			rs = ptmt.executeQuery();
			while (rs.next()) {

				group = new Group();

				group.setGroupId(rs.getInt(1));
				group.setName(rs.getString(2));
				group.setMaxStudents(rs.getInt(3));
				group.setAvailableSpots(rs.getInt(4));
				group.setStartDate(rs.getDate(5));
				group.setFinalDate(rs.getDate(6));
				group.setActiveGroup(rs.getBoolean(7));

				group.setShift(Integer.valueOf(0).equals(rs.getInt(8)) ? Shift.MORNING
						: Shift.AFTERNOON);
				Course c = new Course();
				c.setCourseCode(rs.getInt(9));
				group.setCourse(c);
				Aula a = new Aula();
				a.setIdAula(rs.getInt(10));
				group.setAula(a);

				groups.add(group);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return groups;
	}

	/**
	 * Method to get the non started groups of the course, with vacancies
	 * 
	 * @param idCourse
	 * @return
	 */
	public List<Group> getNoStartedGroupsByCourse(Course course) {
		List<Group> groups = new ArrayList<Group>();

		Group group = null;
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		try {

			String q = "SELECT * FROM GRUPO WHERE idcurso=? AND plazasdisponibles > 0";
			con = getConnection();
			ptmt = con.prepareStatement(q);
			ptmt.setInt(1, course.getCourseCode());
			rs = ptmt.executeQuery();
			while (rs.next()) {
				group = new Group();
				group.setGroupId(rs.getInt(1));
				group.setName(rs.getString(2));
				group.setMaxStudents(rs.getInt(3));
				group.setAvailableSpots(rs.getInt(4));
				group.setStartDate(rs.getDate(5));
				group.setFinalDate(rs.getDate(6));
				group.setActiveGroup(rs.getBoolean(7));
				group.setShift(Integer.valueOf(0).equals(rs.getInt(8)) ? Shift.MORNING
						: Shift.AFTERNOON);

				Aula a = new Aula();
				a.setIdAula(rs.getInt(9));
				group.setAula(a);

				Usuari t = new Usuari();

				t.setNIF(rs.getString(10));
				group.setTeacher(t);

				Course c = new Course();
				c.setCourseCode(rs.getInt(9));
				group.setCourse(c);

				if (group.isActiveGroup()) {

					Date date = new Date();
					if (dateFormat.format(rs.getDate(5)).compareTo(
							dateFormat.format(date)) == 1)
						groups.add(group);
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return groups;
	}

	/**
	 * Finds a group by its Id.
	 * 
	 * @param groupId
	 * @return
	 */
	public Group findByPrimaryKey(int groupId) {

		Group group = null;

		try {
			String querystring = "SELECT * FROM GRUPO WHERE grupoid=? ";
			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			ptmt.setInt(1, groupId);
			rs = ptmt.executeQuery();
			if (rs.next()) {
				group = new Group();
				group.setGroupId(rs.getInt(1));
				group.setName(rs.getString(2));
				group.setMaxStudents(rs.getInt(3));
				group.setAvailableSpots(rs.getInt(4));
				group.setStartDate(rs.getDate(5));
				group.setFinalDate(rs.getDate(6));
				group.setActiveGroup(rs.getBoolean(7));
				group.setShift(Integer.valueOf(0).equals(rs.getInt(8)) ? Shift.MORNING
						: Shift.AFTERNOON);

				Aula a = new Aula();
				a.setIdAula(rs.getInt(9));
				group.setAula(a);

				Usuari t = new Usuari();

				t.setNIF(rs.getString(10));
				group.setTeacher(t);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return group;
	}

	/**
	 * Lists groups according to filters name, shift and activegroup.
	 * 
	 * @param name
	 * @param turno
	 * @param grupovigente
	 * @return
	 */
	public List<Group> find(String name, Shift turno, Boolean grupovigente) {
		List<Group> groups = new ArrayList<Group>();
		Group group = null;
		try {

			String querystring = "SELECT grupoid, nombre, maxalumnos, plazasdisponibles, fechainicio, fechafin, grupovigente, turno, idcurso, idaula "
					+ "FROM GRUPO WHERE '0'='0' ";
			querystring += " AND ( '0'='" + ((name == null) ? 0 : 1)
					+ "' OR  nombre = ? ) ";
			querystring += " AND ( '0'='" + ((turno == null) ? 0 : 1)
					+ "' OR  turno = ? ) ";
			querystring += " AND ( '0'='" + ((grupovigente == null) ? 0 : 1)
					+ "' OR  grupovigente = ? ) ";

			if (grupovigente == null) {
				grupovigente = false;
			}

			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			ptmt.setString(1, name);
			ptmt.setInt(2, Shift.MORNING.equals(turno) ? 0 : 1);
			ptmt.setBoolean(3, grupovigente);

			rs = ptmt.executeQuery();
			while (rs.next()) {

				group = new Group();

				group.setGroupId(rs.getInt(1));
				group.setName(rs.getString(2));
				group.setMaxStudents(rs.getInt(3));
				group.setAvailableSpots(rs.getInt(4));
				group.setStartDate(rs.getDate(5));
				group.setFinalDate(rs.getDate(6));
				group.setActiveGroup(rs.getBoolean(7));

				group.setShift(Integer.valueOf(0).equals(rs.getInt(8)) ? Shift.MORNING
						: Shift.AFTERNOON);
				Course c = new Course();
				c.setCourseCode(rs.getInt(9));
				group.setCourse(c);
				Aula a = new Aula();
				a.setIdAula(rs.getInt(10));
				group.setAula(a);

				groups.add(group);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return groups;
	}

	/**
	 * Finds all groups taught by a particular teacher
	 * 
	 * @return
	 */
	public List<Group> findGroupsByProf(String profId) {
		List<Group> groups = new ArrayList<Group>();
		Group group = null;
		try {
			String querystring = "SELECT grupoid, nombre, maxalumnos, plazasdisponibles, fechainicio, fechafin, grupovigente, turno, idcurso, idaula FROM GRUPO WHERE idprofesor = ? AND grupovigente = true ";
			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			ptmt.setString(1, profId);

			rs = ptmt.executeQuery();
			while (rs.next()) {

				group = new Group();

				group.setGroupId(rs.getInt(1));
				group.setName(rs.getString(2));
				group.setMaxStudents(rs.getInt(3));
				group.setAvailableSpots(rs.getInt(4));
				group.setStartDate(rs.getDate(5));
				group.setFinalDate(rs.getDate(6));
				group.setActiveGroup(rs.getBoolean(7));

				group.setShift(Integer.valueOf(0).equals(rs.getInt(8)) ? Shift.MORNING
						: Shift.AFTERNOON);
				Course c = new Course();
				c.setCourseCode(rs.getInt(9));
				group.setCourse(c);
				Aula a = new Aula();
				a.setIdAula(rs.getInt(10));
				group.setAula(a);

				groups.add(group);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return groups;
	}

	/**
	 * Returns a group with a certain name and shift.
	 * 
	 * @param nombre
	 * @param shift
	 * @return
	 */
	public Group findGroupByNameAndShift(String nombre, Shift shift) {

		Group group = null;
		try {
			String querystring = "SELECT grupoid, nombre, maxalumnos, plazasdisponibles, fechainicio, fechafin, grupovigente, turno, idcurso FROM GRUPO  WHERE grupovigente = ? AND nombre=? AND turno=? ";
			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			ptmt.setBoolean(1, true);
			ptmt.setString(2, nombre);
			ptmt.setInt(3, Shift.MORNING.equals(shift) ? 0 : 1);
			rs = ptmt.executeQuery();
			if (rs.next()) {
				group = new Group();
				group.setGroupId(rs.getInt(1));
				group.setName(rs.getString(2));
				group.setMaxStudents(rs.getInt(3));
				group.setAvailableSpots(rs.getInt(4));
				group.setStartDate(rs.getDate(5));
				group.setFinalDate(rs.getDate(6));
				group.setActiveGroup(rs.getBoolean(7));

				group.setShift(Integer.valueOf(0).equals(rs.getInt(8)) ? Shift.MORNING
						: Shift.AFTERNOON);
				Course c = new Course();
				c.setCourseCode(rs.getInt(9));
				group.setCourse(c);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return group;
	}

	/**
	 * Updates the available spots in a group.
	 * 
	 * @param group
	 */
	public void updateSpots(Group group) {
		try {
			String querystring = "UPDATE GRUPO SET  plazasdisponibles=? "
					+ "WHERE grupoid=?";

			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			ptmt.setInt(1, group.getAvailableSpots());

			ptmt.setInt(2, group.getGroupId());

			ptmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

	}

	/**
	 * Updates resources (teacher and room) in a group.
	 * 
	 * @param group
	 * @param aula
	 * @param prof
	 */
	public void updateResources(Group group, Aula aula, Usuari prof) {
		try {
			String querystring = "UPDATE GRUPO SET  idaula=?, idprofesor=? "
					+ "WHERE grupoid=? ";

			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			ptmt.setInt(1, aula.getIdAula());
			ptmt.setString(2, prof.getNIF());

			ptmt.setInt(3, group.getGroupId());

			ptmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

	}

	/**
	 * Finds all groups taught in a particular room
	 * 
	 * @return
	 */
	public List<Group> findGroupsByRoom(int idAula) {
		List<Group> groups = new ArrayList<Group>();
		Group group = null;
		try {
			String querystring = "SELECT grupoid, nombre, maxalumnos, plazasdisponibles, fechainicio, fechafin, grupovigente, turno, idcurso, idaula FROM GRUPO WHERE idAula = ? AND grupovigente = true ";
			con = getConnection();
			ptmt = con.prepareStatement(querystring);
			ptmt.setInt(1, idAula);
			// ptmt.setb

			rs = ptmt.executeQuery();
			while (rs.next()) {

				group = new Group();

				group.setGroupId(rs.getInt(1));
				group.setName(rs.getString(2));
				group.setMaxStudents(rs.getInt(3));
				group.setAvailableSpots(rs.getInt(4));
				group.setStartDate(rs.getDate(5));
				group.setFinalDate(rs.getDate(6));
				group.setActiveGroup(rs.getBoolean(7));

				group.setShift(Integer.valueOf(0).equals(rs.getInt(8)) ? Shift.MORNING
						: Shift.AFTERNOON);
				Course c = new Course();
				c.setCourseCode(rs.getInt(9));
				group.setCourse(c);
				Aula a = new Aula();
				a.setIdAula(rs.getInt(10));
				group.setAula(a);

				groups.add(group);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ptmt != null)
					ptmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return groups;
	}
}
